package org.light.framework;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;

import org.light.framework.bean.OperateResult;
import org.light.framework.bean.PageArgs;
import org.light.framework.bean.ResultColumn;
import org.light.framework.bean.ResultOrder;
import org.light.framework.bean.SearchArgs;
import org.light.framework.bean.UpdateColumn;
import org.light.framework.dao.DbType;
import org.light.framework.dao.SQLHelper;
import org.light.framework.orm.BelongsInfo;
import org.light.framework.orm.ColumnInfo;
import org.light.framework.orm.DataColumn;
import org.light.framework.orm.DataColumnCollection;
import org.light.framework.orm.DataRow;
import org.light.framework.orm.DataTable;
import org.light.framework.orm.DbMapper;
import org.light.framework.orm.ManyToManyAction;
import org.light.framework.orm.SQLBuilder;
import org.light.framework.orm.SQLLoger;
import org.light.framework.util.ReflectionUtil;
import org.light.framework.util.StopWatch;
import org.light.framework.util.StringUtil;

/**
 * 实体查询类
 * 
 * @author de.cel
 *
 */
public class EntityMediator {

	private static final int MAX_PARAMETER_QUANTITY = 1500;

	public static <T> OperateResult create(T obj) {

		OperateResult result = new OperateResult();

		try {

			ColumnInfo primaryKey = DbMapper.getPrimaryKey(obj.getClass());

			ArrayList<Object> params = new ArrayList<Object>();

			String sql = SQLBuilder.buildInsertSql(obj, null, params);
			sql += SQLBuilder.buildManyToManySql(obj, primaryKey, ManyToManyAction.Create, null);

			SQLLoger.write(sql, params.toArray());
			SQLHelper.executeNonQuery(sql, params.toArray());

			result.isSucced = true;
			result.message = "新增成功";

		} catch (Exception e) {
			result.isSucced = true;
			result.message = "新增失败：" + e;
		}

		return result;
	}

	/**
	 * 批量新增
	 * 
	 * @param objs
	 * @return
	 * @throws Exception
	 */
	public static <T> OperateResult create(ArrayList<T> objs) {

		OperateResult result = new OperateResult();
		StringBuilder builder = new StringBuilder();
		ArrayList<Object> listParam = new ArrayList<Object>();
		
		try {
			if (objs == null || objs.isEmpty()) {
				result.isSucced = false;
				result.message = "objs 不能为空";
				return result;
			}

			Class<?> type = objs.get(0).getClass();
			// 获取表名和主键
			String tableName = DbMapper.getTableName(type);
			ColumnInfo primaryKey = DbMapper.getPrimaryKey(type);

			int countSucced = 0;
			int length = objs.size();

			DbType dbType = SQLHelper.GetDbType();

			if (dbType == DbType.SqlServer) {

				// StopWatch sw=new StopWatch();
				// sw.start();

				for (int i = 0; i < length; i++) {
					builder.append(SQLBuilder.buildInsertSql(objs.get(i), tableName, listParam));
					builder.append(
							SQLBuilder.buildManyToManySql(objs.get(i), primaryKey, ManyToManyAction.Create, null));

					if ((listParam.size() >= MAX_PARAMETER_QUANTITY || i == length - 1) && builder.length() > 0) {

						SQLLoger.write(builder.toString(), listParam.toArray());
						countSucced += SQLHelper.executeNonQuery(builder.toString(), listParam.toArray());

						builder.delete(0, builder.length());
						listParam.clear();
					}
				}

			} else if (dbType == DbType.MySql) {
				
				builder.append(SQLBuilder.buildInsertSql4MySql(objs, tableName, listParam));
				for (int i = 0; i < length; i++) {

					builder.append(SQLBuilder.buildManyToManySql(objs.get(i), primaryKey, ManyToManyAction.Create, null));
				}

				SQLLoger.write(builder.toString(), listParam.toArray());
				countSucced += SQLHelper.executeNonQuery(builder.toString(), listParam.toArray());
			}

			// sw.stop();
			// System.out.println("SQLHelper：耗时："+sw.getTime());

			if (countSucced > 0) {
				result.isSucced = true;
				result.message = "新增成功";
			}

		} catch (Exception e) {
			result.isSucced = false;
			result.message = "新增失敗，" + e.toString();
		}

		return result;
	}

	/**
	 * 删除
	 * 
	 * @param obj
	 * @return
	 */
	public static <T> OperateResult delete(T obj) {

		OperateResult result = new OperateResult();
		try {

			ArrayList<Object> params = new ArrayList<Object>();

			String tableName = DbMapper.getTableName(obj.getClass());
			ColumnInfo primaryKey = DbMapper.getPrimaryKey(obj.getClass());

			Field f = obj.getClass().getDeclaredField(primaryKey.propertyName);
			f.setAccessible(true);
			Object value = f.get(obj);

			String sql = SQLBuilder.buildDeleteSql(tableName, primaryKey, value, params);
			sql += SQLBuilder.buildManyToManySql(obj, primaryKey, ManyToManyAction.Delete, null);

			SQLLoger.write(sql, params.toArray());

			SQLHelper.executeNonQuery(sql, params.toArray());
			result.isSucced = true;
			result.message = "删除成功";

		} catch (Exception e) {
			result.isSucced = true;
			result.message = "删除失败：" + e;
		}
		return result;
	}

	/**
	 * 删除
	 * 
	 * @param obj
	 * @return
	 */
	public static <T> OperateResult delete(ArrayList<T> objs) {

		OperateResult result = new OperateResult();
		try {

			StringBuilder builder = new StringBuilder();
			List<Object> params = new ArrayList<Object>();

			String tableName = DbMapper.getTableName(objs.get(0).getClass());
			ColumnInfo primaryKey = DbMapper.getPrimaryKey(objs.get(0).getClass());
			List<Object> values=new ArrayList<Object>();
			
			for (T obj : objs) {
				
				Field f = obj.getClass().getDeclaredField(primaryKey.propertyName);
				f.setAccessible(true);
				Object value = f.get(obj);

				values.add(value);
				builder.append(SQLBuilder.buildManyToManySql(obj, primaryKey, ManyToManyAction.Delete, null));
			}
			
			builder.append(SQLBuilder.buildDeleteSql(tableName, primaryKey, values, params));

			String sql = builder.toString();

			SQLLoger.write(sql, params.toArray());

			SQLHelper.executeNonQuery(sql, params.toArray());
			result.isSucced = true;
			result.message = "删除成功";

		} catch (Exception e) {
			result.isSucced = true;
			result.message = "删除失败：" + e;
		}
		return result;
	}

	/**
	 * 批量删除
	 * 
	 * @param type
	 * @param ids
	 * @return
	 * @throws Exception
	 */
	public static <T> OperateResult delete(Class<T> type, Object... ids) {
		OperateResult result = new OperateResult();
		try {

			if (ids == null || ids.length == 0) {
				result.isSucced = false;
				result.message = "没有指定要删除的记录";
				return result;
			}

			String tableName = DbMapper.getTableName(type);
			ColumnInfo primaryKey = DbMapper.getPrimaryKey(type);
			if (primaryKey == null)
				throw new Exception(String.format("删除失败，类型 %s 未映射主键 PrimaryKeyAttribute", type.getName()));

			List<Object> listParam = new ArrayList<Object>();

			StringBuilder builder = new StringBuilder();
			int length = ids.length;
			int countSucced = 0;

			StopWatch sw=new StopWatch();
			sw.start();
			List<Object> values = new ArrayList<Object>();

			for (int i = 0; i < length; i++) {

				if (StringUtil.isNotNullOrEmpty(ids[i])) {
					T obj = type.newInstance();

					values.add(ids[i]);

					ReflectionUtil.setPropertyValue(obj, primaryKey.propertyName, ids[i]);
					builder.append(SQLBuilder.buildManyToManySql(obj, primaryKey, ManyToManyAction.Delete, null));
				}
			}
			builder.append(SQLBuilder.buildDeleteSql(tableName, primaryKey, values, listParam));

			SQLLoger.write(builder.toString(), listParam.toArray());
			countSucced += SQLHelper.executeNonQuery(builder.toString(), listParam.toArray());
			sw.stop();
			if (countSucced > 0) {
				result.isSucced = true;
				result.message = "批量删除 " + countSucced + " 条记录，耗时："+sw.getElapsedMilliseconds()+" ms";
			} else {
				result.isSucced = false;
				result.message = "删除失败，没有匹配的记录";
			}

		} catch (Exception e) {
			result.isSucced = false;
			result.message = "删除失败，" + e.toString();
		}

		return result;
	}
	/**
	 * 更新
	 * 
	 * @param obj
	 * @return
	 * @throws Exception
	 */
	public static <T> OperateResult update(T obj) {
		return update(obj, null);
	}

	/**
	 * 更新
	 * 
	 * @param obj
	 * @param updateColumn
	 * @return
	 * @throws Exception
	 */
	public static <T> OperateResult update(T obj, UpdateColumn updateColumn) {

		OperateResult result = new OperateResult();
		try {
			ArrayList<Object> params = new ArrayList<Object>();
			String sql = SQLBuilder.buildUpdateSql(obj, null, null, null, updateColumn, params);

			if (updateColumn != null && updateColumn.isManyToManyUpdate) {
				// 获取主键
				ColumnInfo primaryKey = DbMapper.getPrimaryKey(obj.getClass());
				sql += SQLBuilder.buildManyToManySql(obj, primaryKey, ManyToManyAction.Update, updateColumn);
			}

			SQLLoger.write(sql, params.toArray());

			SQLHelper.executeNonQuery(sql, params.toArray());
			result.isSucced = true;
			result.message = "修改成功";

		} catch (Exception e) {
			result.isSucced = false;
			result.message = "修改失败：" + e;
		}
		return result;
	}

	/**
	 * 批量更新
	 * 
	 * @param objs
	 * @param saveColumn
	 *            需要保存的列
	 * @return
	 * @throws Exception
	 */
	public static <T> OperateResult update(ArrayList<T> objs, UpdateColumn saveColumn) {
		OperateResult result = new OperateResult();

		try {

			if (objs == null || objs.isEmpty()) {
				result.isSucced = false;
				result.message = "objs 不能为空";
				return result;
			}

			Class<?> type = objs.get(0).getClass();

			// 获取表名
			String tableName = DbMapper.getTableName(type);
			// 获取主键
			ColumnInfo primaryKey = DbMapper.getPrimaryKey(type);
			// 获取系统类型列
			ArrayList<ColumnInfo> columns = DbMapper.getColumns(type);

			StringBuilder builder = new StringBuilder();
			ArrayList<Object> listParam = new ArrayList<Object>();

			if (saveColumn == null)
				saveColumn = new UpdateColumn(type);

			if (!saveColumn.include(primaryKey.propertyName))
				saveColumn.add(primaryKey.propertyName);

			int countSucced = 0;
			int length = objs.size();
			for (int i = 0; i < length; i++) {

				builder.append(
						SQLBuilder.buildUpdateSql(objs.get(i), tableName, primaryKey, columns, saveColumn, listParam));

				if (saveColumn.isManyToManyUpdate)
					builder.append(SQLBuilder.buildManyToManySql(objs.get(i), primaryKey, ManyToManyAction.Update,
							saveColumn));

				if ((listParam.size() >= MAX_PARAMETER_QUANTITY || i == length - 1) && builder.length() > 0) {

					SQLLoger.write(builder.toString(), listParam.toArray());
					countSucced += SQLHelper.executeNonQuery(builder.toString(), listParam.toArray());

					builder.delete(0, builder.length());
					listParam.clear();
				}
			}

			if (countSucced > 0) {
				result.isSucced = true;
				result.message = "更新成功";
			}

		} catch (Exception e) {
			result.isSucced = false;
			result.message = e.toString();
		}
		return result;
	}

	public static <T> T get(String id) {

		return null;
	}

	public static <T> ArrayList<T> getAll(Class<T> c, SearchArgs searchArgs, ResultOrder resultOrder,
			ResultColumn resultColumn) {

		return getAll(c, searchArgs, resultOrder, resultColumn, 0);
	}

	public static <T> ArrayList<T> getAll(Class<T> c, SearchArgs searchArgs, ResultOrder resultOrder,
			ResultColumn resultColumn, int resultCount) {

		try {
			DataTable dt = getEntitiesTable(c, searchArgs, resultOrder, resultColumn, resultCount);
			return convertToEntity(c, dt);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ArrayList<T>();
	}

	public static <T> ArrayList<T> search(Class<T> c, SearchArgs searchArgs, PageArgs pageArgs, ResultOrder resultOrder,
			ResultColumn resultColumn) {

		try {
			DataTable dt = getEntitiesTable(c, searchArgs, pageArgs, resultOrder, resultColumn);
			return convertToEntity(c, dt);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ArrayList<T>();
	}

	public static <T> T get(Class<T> c, String id) {
		return get(c, id, new ResultColumn());
	}

	public static <T> T get(Class<T> c, String id, ResultColumn resultColumn) {

		try {
			T obj = c.newInstance();

			String select = SQLBuilder.buildSelectSql(c, true, resultColumn);
			String from = SQLBuilder.buildFromSql(c, resultColumn, null);
			String where = " WHERE ";

			LinkedHashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(c);

			ColumnInfo primaryKey = DbMapper.getPrimaryKey(c);

			if (dicBelongs != null && !dicBelongs.isEmpty())
				where += DbMapper.PRIMARY_TABLE_ALIAS + "." + primaryKey.name + "=?";
			else
				where += primaryKey.name + "=?";
			String sql = String.format("SELECT %s FROM %s %s", select, from, where);

			// 1对多查询
			// LinkedHashMap<String, ManyToManyInfo> dicManyToMany =
			// DbMapper.getManyToMany(c);
			//
			// if (dicManyToMany != null && !dicManyToMany.isEmpty()) {
			// StringBuilder builder = new StringBuilder();
			// String tableName = DbMapper.getTableName(c);
			// for (String key : dicManyToMany.keySet()) {
			// ManyToManyInfo many = dicManyToMany.get(key);
			//
			// // 关联表
			// String tableName_ref = DbMapper.getTableName(many.entityType);
			// ColumnInfo refKey = DbMapper.getPrimaryKey(many.entityType);
			//
			// builder.append(
			// " SELECT " + tableName_ref + ".* FROM " + tableName + " " +
			// DbMapper.PRIMARY_TABLE_ALIAS);
			// builder.append(" JOIN " + many.table + " " + many.Alias);
			// builder.append(" ON " + many.Alias + "." + many.columnKey + "=" +
			// DbMapper.PRIMARY_TABLE_ALIAS + "."
			// + primaryKey.name);
			//
			// builder.append(" JOIN " + tableName_ref + " " + tableName_ref);
			// builder.append(" ON " + many.Alias + "." + many.columnRef + "=" +
			// tableName_ref + "." + refKey.name);
			// }
			//
			// where = " WHERE " + DbMapper.PRIMARY_TABLE_ALIAS + "." +
			// primaryKey.name + "=?";
			// builder.append(where);
			//
			// sql += ";" + builder.toString();
			// }

			SQLLoger.write(sql, id);
			DataTable dt = SQLHelper.fillDataTable(sql, id);

			if (dt != null && dt.getRows().size() > 0) {
				obj = convertToEntity(c, dt).get(0);

				// 1对多
				/*
				 * int tableCount = ds.Tables.Count; if (tableCount > 1) {
				 * foreach (var key in dicManyToMany.Keys) { ManyToMany many =
				 * dicManyToMany[key];
				 * 
				 * //将多对多表的集合项 for (int i = 1; i < tableCount; i++) { if
				 * (ds.Tables[i].Rows.Count > 0) { IList refs =
				 * ConvertToEntity(many.Type, ds.Tables[i]);
				 * ReflectionUtil.SetPropertyValue(obj, key, refs); } } }
				 * 
				 * }
				 */
			}

			return obj;
		} catch (Exception e) {

			e.printStackTrace();

			return null;
		}

	}

	public static <T> T get(Class<T> c, SearchArgs searchArgs, ResultColumn resultColumn) {

		ArrayList<T> list = getAll(c, searchArgs, null, resultColumn);

		if (list == null || list.isEmpty())
			return list.get(0);
		else
			return null;
	}

	/**
	 * 金额求和
	 * 
	 * @param type
	 * @param columnName
	 *            数据库列名
	 * @param searchArgs
	 *            查询参数
	 * @return
	 */
	public static Double sum(Class<?> type, String propertyName, SearchArgs searchArgs) {
		double sum = 0;
		try {
			if (searchArgs == null)
				searchArgs = new SearchArgs();

			String where = searchArgs.toString(type);
			String columnName = DbMapper.getColumnName(type, propertyName); // 获取属性名对应的列表

			ResultColumn resultColumn = new ResultColumn();
			resultColumn.add(propertyName);
			String from = SQLBuilder.buildFromSql(type, null, searchArgs);
			ArrayList<Object> listParams = searchArgs.getDataParameters(type);

			String sql = String.format("SELECT SUM(%s) FROM %s %s", columnName, from, where);

			SQLLoger.write(sql, listParams.toArray());

			Object result = SQLHelper.executeScalar(sql, listParams.toArray());
			return StringUtil.toDouble(result);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sum;
	}

	/**
	 * 返回记录条数
	 * 
	 * @param type
	 * @param searchArgs
	 * @return
	 */
	public static int count(Class<?> type, SearchArgs searchArgs) {
		return count(type, searchArgs, new ResultColumn());
	}

	/**
	 * 返回记录条数
	 * 
	 * @param type
	 * @param searchArgs
	 *            查询参数，允许为null
	 * @param resultColumn
	 *            需要查询的结果属性集合 允许为null
	 * @return
	 */
	public static int count(Class<?> type, SearchArgs searchArgs, ResultColumn resultColumn) {
		int count = 0;
		try {

			if (searchArgs == null)
				searchArgs = new SearchArgs();

			String where = searchArgs.toString(type);
			String from = SQLBuilder.buildFromSql(type, resultColumn, searchArgs);
			ArrayList<Object> listParams = searchArgs.getDataParameters(type);

			String sql = String.format("SELECT COUNT(*) FROM %s %s", from, where);

			SQLLoger.write(sql, listParams.toArray());

			Object result = SQLHelper.executeScalar(sql, listParams.toArray());
			count = StringUtil.toInt(result);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	/**
	 * 分页查询，返回转实体用的DataTable
	 * 
	 * @param searchArgs
	 * @param pageArgs
	 * @param resultOrder
	 * @param resultColumn
	 * @return
	 * @throws Exception
	 */
	private static DataTable getEntitiesTable(Class<?> c, SearchArgs searchArgs, ResultOrder resultOrder,
			ResultColumn resultColumn, int resultCount) throws Exception {
		if (searchArgs == null)
			searchArgs = new SearchArgs();
		if (resultOrder == null)
			resultOrder = new ResultOrder();

		String topSql = resultCount > 0 ? " TOP " + resultCount + " " : "";
		String selectSql = SQLBuilder.buildSelectSql(c, false, resultColumn);
		String fromSql = SQLBuilder.buildFromSql(c, resultColumn, searchArgs);
		String whereSql = searchArgs.toString(c);
		String orderSql = resultOrder.toString(c);

		ArrayList<Object> listParams = searchArgs.getDataParameters(c);

		// 查询结果集
		StringBuilder builder = new StringBuilder();
		builder.append(" SELECT ");
		builder.append(topSql);
		builder.append(selectSql);
		builder.append(" FROM ");
		builder.append(fromSql);
		builder.append(whereSql);
		builder.append(" ORDER BY ");
		builder.append(orderSql);

		SQLLoger.write(builder.toString(), listParams.toArray());

		return SQLHelper.fillDataTable(builder.toString(), listParams.toArray());
	}

	/**
	 * 分页查询，返回转实体用的DataTable
	 * 
	 * @param searchArgs
	 * @param pageArgs
	 * @param resultOrder
	 * @param resultColumn
	 * @return
	 * @throws Exception
	 */
	private static DataTable getEntitiesTable(Class<?> c, SearchArgs searchArgs, PageArgs pageArgs,
			ResultOrder resultOrder, ResultColumn resultColumn) throws Exception {
		if (searchArgs == null)
			searchArgs = new SearchArgs();
		if (resultOrder == null)
			resultOrder = new ResultOrder();
		if (pageArgs == null)
			throw new Exception("分页参数 PageArgs 不能为空");

		String selectSql = SQLBuilder.buildSelectSql(c, false, resultColumn);
		String fromSql = SQLBuilder.buildFromSql(c, resultColumn, searchArgs);
		String whereSql = searchArgs.toString(c);
		String orderSql = resultOrder.toString(c);

		// 获取总记录条数
		String countSql = " SELECT COUNT(*) FROM " + fromSql + " " + whereSql;

		ArrayList<Object> listParams = searchArgs.getDataParameters(c);

		SQLLoger.write(countSql, listParams.toArray());

		pageArgs.totalResults = StringUtil.toInt(SQLHelper.executeScalar(countSql, listParams.toArray()));

		// 查询结果集

		StringBuilder builder = new StringBuilder();
		builder.append(" SELECT ");
		builder.append(selectSql);
		builder.append(" FROM ");
		builder.append(fromSql);
		builder.append(whereSql);
		builder.append(" ORDER BY ");
		builder.append(orderSql);

		int startIndex = (pageArgs.pageIndex - 1) * pageArgs.pageSize;

		DbType dbType = SQLHelper.GetDbType();
		if (dbType == DbType.SqlServer)
			builder.append(" OFFSET " + startIndex + " ROWS FETCH NEXT " + pageArgs.pageSize + " ROWS ONLY");
		else // if(dbType==DbType.MySql)
			builder.append(" LIMIT " + startIndex + " ," + pageArgs.pageSize);

		SQLLoger.write(builder.toString(), listParams.toArray());

		return SQLHelper.fillDataTable(builder.toString(), listParams.toArray());
	}

	/**
	 * 将数据表转实体
	 * 
	 * @param objType
	 * @param dt
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private static <T> ArrayList<T> convertToEntity(Class<T> objType, DataTable dt) throws Exception {
		ArrayList<T> list = new ArrayList<T>();
		int idx = 0;
		int count = dt.getTotalCount();

		ArrayList<ColumnInfo> columns = DbMapper.getColumns(objType, 0);
		LinkedHashMap<String, Object> values = new LinkedHashMap<String, Object>();

		DataColumnCollection dtColumns = dt.getColumns();

		// 逐行转换
		for (int i = 0; i < count; i++) {
			values.clear();
			DataRow dr = dt.getRows().get(i);
			for (DataColumn col : dtColumns) {
				// 将列名替换成属性名
				String cname = col.getColumnName().replace(DbMapper.PREFIX_TABLE_ALIAS + idx + "_", "");
				ColumnInfo c = ColumnInfo.findByName(columns, cname);
				if (c != null)
					values.put(c.propertyName, dr.getValue(col.getColumnName()));
			}

			// 为 obj 基本类型赋值
			Object obj = objType.newInstance();

			ReflectionUtil.setPropertyValue(obj, values);

			list.add((T) obj);
		}

		LinkedHashMap<String, BelongsInfo> dicBelongs = DbMapper.getBelongsDic(objType);
		if (dicBelongs != null && !dicBelongs.isEmpty()) { // 遍历关联属性
			for (Entry<String, BelongsInfo> entry : dicBelongs.entrySet()) {
				idx++;
				BelongsInfo bc = entry.getValue();

				int cascade = entry.getKey().split("\\.").length;
				columns = DbMapper.getColumns(bc.entityType, cascade);

				// 逐行转换
				for (int i = 0; i < count; i++) {
					values.clear();
					DataRow dr = dt.getRows().get(i);

					for (DataColumn col : dt.getColumns()) {
						String cname = col.getColumnName().replace(bc.alias + "_", "");
						ColumnInfo c = ColumnInfo.findByName(columns, cname);
						if (c != null)
							values.put(entry.getKey() + "." + c.propertyName, dr.getValue(col.getColumnName()));
					}
					ReflectionUtil.setPropertyValue(list.get(i), values);
				}
			}
		}
		return list;
	}

}
